package com.chakid.dbf;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;

import com.linuxense.javadbf.DBFField;
import com.linuxense.javadbf.DBFReader;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;


/**
 * 读取DBF文件，并拼凑成sql 插入语句 实现数据的插入
 * @author Yuan
 *
 */
public class ReadDBF {

	private static String table;// 表格名称;
	private static StringBuffer params;// 记录表格各字段
	private static StringBuffer values;// 记录每一行数据
	
	private static List<String> sql;// 记录所有行的数据

	private static Connection con = null;
	private static java.sql.Statement stm = null;
	private static ResultSet res = null;
	
	
	private static Integer csny_Nu; //记录出生年月有问题的位置
	private static Integer GKCJX04;
	
	private static Integer ZYTJFC;

	public static void readDBF(String path) throws SQLException, IOException {

		table = Const.LAST_PATH;
		params = new StringBuffer("(");
		values = new StringBuffer();
		sql = new ArrayList<String>();

		InputStream fis = null;
		try {
			// 读取文件的输入流
			fis = new FileInputStream(path);
			// 根据输入流初始化一个DBFReader实例，用来读取DBF文件信息
			DBFReader reader = new DBFReader(fis);
			
			reader.setCharactersetName("GBK");
			// 调用DBFReader对实例方法得到path文件中字段的个数
			int fieldsCount = reader.getFieldCount();
			// 取出字段信息
			for (int i = 0; i < fieldsCount; i++) {
				DBFField field = reader.getField(i);
				// 字段名
//			 	System.out.println(field.getName());
				params.append(field.getName() + ",");
				
				//出生年月时间格式有问题
				
				if(field.getName().equals("CSNY")){
					csny_Nu = i+1;
				//	System.out.println("csny_Nu===="+csny_Nu);
				}
				if(field.getName().equals("GKCJX04")){
					GKCJX04 = i+1;
//					System.out.println("GKCJX04===="+GKCJX04);
				}
				if(field.getName().equals("ZYTJFC")){
					ZYTJFC = i+1;
//					System.out.println("GKCJX04===="+GKCJX04);
				}
			}
		
			
			
			
			params.deleteCharAt(params.length() - 1);
			params.append(")");
//			System.out.println("params===" + params);
			Object[] rowValues;
			// 一条条取出path文件中记录
			while ((rowValues = reader.nextRecord()) != null) {
				values.append("(");
				for (int i = 0; i < rowValues.length; i++) {

					//出生年月的字段转换
					if(i+1==csny_Nu){
						
						 
						 SimpleDateFormat sdf = new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy", Locale.US);


						 //java.util.Date对象
						 Date date = (Date) sdf.parse(rowValues[i].toString());



						 String formatStr = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
						
						 rowValues[i] = formatStr;
						
					//	 System.out.println(rowValues[i]);
					}
					
					
//					if(i+1==ZYTJFC){
//						rowValues[i] = "00000000000";
//					}
					
					
					
					
					// 字段值
					if (rowValues[i] == null || rowValues[i].equals("") || rowValues[i] == "nul") {
						values.append( "'" + "null" +"'" + ",");
					}
					
					else {

						values.append("'" + rowValues[i].toString().trim() + "',");
					}
				}

				values.deleteCharAt(values.length() - 1);
				values.append("),");

			}
			
			String ValueStr = values.toString();
			
			ValueStr = ValueStr.substring(0,ValueStr.length()-1);
			
			// 生成插入语句字符串
			
			
			sql.add("insert into " + table + params.toString() + " values "+  ValueStr);
		
			System.out.println("sqlCrea==="+sql);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				fis.close();
			} catch (Exception e) {
			}
		}
			
		ReadDBF.dbf_Import(sql);
//		ReadDBF.ex_Insert(sql.toString());
		
		 //关闭输入流
        fis.close();
        //关闭数据库连接
        DBConnect.close(null, stm, con);
		

	}

	public static void dbf_Import(List sql) throws SQLException {
		// 打开数据库连接
		con = DBConnect.getConnection();

		stm = con.createStatement();
		int i = 0;
		for (; i < sql.size(); i++) {
			// 将sql语句加入批处理中
			stm.addBatch((String) sql.get(i));
		}
		// 执行批处理
		System.out.println("开始插入数据");
		stm.executeBatch();
		System.out.println("插入数据成功");

	}
	
	
	public static void ex_Insert(String sql) throws SQLException{
	     
		 Statement stmt = null;
	    
		 con = DBConnect.getConnection();
		 stmt = (Statement) con.createStatement();
		 stmt.execute(sql);
	
	}
	

	public static void main(String[] args) throws SQLException, IOException {
				ReadDBF.readDBF(Const.PATH_Add);

	}
}
